package com.xiaoluo.lottery.dao;

import com.xiaoluo.lottery.entity.HbInfo;
import com.xiaoluo.lottery.entity.Order;
import com.xiaoluo.lottery.filter.HbFilter;
import com.xiaoluo.lottery.vo.DataPage;
import com.xiaoluo.lottery.vo.HbInfoSumVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * Project : lottery
 * Package : com.xiaoluo.lottery.dao
 * Created by Willian.
 * Date  : 2016/4/6 22:43.
 */
@Component
public class HbInfoJdbcDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static final Logger log= LoggerFactory.getLogger(HbInfoJdbcDao.class);

    /**
     *
     * @param hbCode
     * @return
     */
    public HbInfo select(String hbCode){
        String sql="SELECT * FROM lottery_hb where hb_code='"+hbCode+"'";
        try{
            return jdbcTemplate.queryForObject(sql,rowMapper);
        }catch (EmptyResultDataAccessException e){
            log.error("Get empty HbInfo:hbCode={}",hbCode);
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 获取发送失败的红包记录
     * @return
     */
    public List<HbInfo> select(Integer status,Date date,Integer minAmount,Integer maxAmount){
        String sql="SELECT * FROM lottery_hb where status=? and update_at<? and total_amount>=? AND total_amount<=? ORDER BY id DESC limit 0,500";
        return jdbcTemplate.query(sql,new Object[]{status,date,minAmount,maxAmount},rowMapper);
    }

    /**
     * 获取发送成功记录
     * @param status
     * @param date
     * @return
     */
    public List<HbInfo> select(Integer status,Date date){
        String sql="SELECT * FROM lottery_hb where status=? and update_at<?  ORDER BY id DESC limit 0,500";
        return jdbcTemplate.query(sql,new Object[]{status,date},rowMapper);
    }

    /**
     * 红包统计
     * @param filter
     * @return
     */
    public HbInfoSumVo sum(HbFilter filter){
        StringBuilder query=new StringBuilder("SELECT COUNT(1) total,SUM(total_amount) totalAmount,SUM(refund_amount) refundAmount FROM lottery_hb ");

        StringBuilder condition=new StringBuilder(" where 1=1 ");
        if(!StringUtils.isEmpty(filter.getStatus())){
            condition.append(" and status ='").append(filter.getStatus()).append("'");
        }
        if(!StringUtils.isEmpty(filter.getNickName())){
            condition.append(" and nick_name like '%").append(filter.getNickName()).append("%'");
        }
        if(!StringUtils.isEmpty(filter.getStartDate())){
            condition.append(" and create_at >= '").append(filter.getStartDate()).append("'");
        }
        if(!StringUtils.isEmpty(filter.getEndDate())){
            condition.append(" and create_at <= '").append(filter.getEndDate()).append("'");
        }

        query.append(condition);


        HbInfoSumVo records=jdbcTemplate.queryForObject(query.toString(), sumRowMapper);

        return records;
    }
    /**
     *
     * @param filter
     * @return
     */
    public DataPage<HbInfo> select(HbFilter filter){
        StringBuilder query=new StringBuilder("SELECT * FROM lottery_hb ");
        StringBuilder count=new StringBuilder("SELECT count(1) FROM lottery_hb ");
        StringBuilder condition=new StringBuilder(" where 1=1 ");
        if(!StringUtils.isEmpty(filter.getStatus())){
            condition.append(" and status ='").append(filter.getStatus()).append("'");
        }
        if(!StringUtils.isEmpty(filter.getNickName())){
            condition.append(" and nick_name like '%").append(filter.getNickName()).append("%'");
        }
        if(!StringUtils.isEmpty(filter.getStartDate())){
            condition.append(" and create_at >= '").append(filter.getStartDate()).append("'");
        }
        if(!StringUtils.isEmpty(filter.getEndDate())){
            condition.append(" and create_at <= '").append(filter.getEndDate()).append("'");
        }

        query.append(condition);
        count.append(condition);
        query.append(" ORDER BY id DESC");
        //query.append(" limit ").append(filter.getStart()).append(",").append(filter.getEnd());

        long total=jdbcTemplate.queryForObject(count.toString(),Long.class);
        List<HbInfo> records=jdbcTemplate.query(query.toString(),rowMapper);
        return new DataPage<>(records,total,filter.getPageIndex(),filter.getPageSize());
    }

    /**
     *
     * @param hb
     * @return
     */
    public int insert(HbInfo hb){

        String sql="INSERT INTO lottery_hb" +
                "            (" +
                "             hb_code," +
                "             hb_type,"+
                "             total_num,"+
                "             total_amount,"+
                "             amount,"+
                "             send_time,"+
                "             send_name," +
                "             openid," +
                "             nick_name," +
                "             wishing," +
                "             client_ip," +
                "             act_name," +
                "             remark," +
                "             STATUS," +
                "             create_at" +
                "             )" +
                "VALUES (" +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?," +
                "        ?);";

        Object[] params=new Object[]{
                hb.getHbCode(),
                hb.getHbType(),
                hb.getTotalNum(),
                hb.getTotalAmount(),
                hb.getAmount(),
                hb.getSendTime(),
                hb.getSendName(),
                hb.getOpenid(),
                hb.getNickName(),
                hb.getWishing(),
                hb.getClientIp(),
                hb.getActName(),
                hb.getRemark(),
                hb.getStatus(),
                hb.getCreateAt()
        };
        int i=jdbcTemplate.update(sql,params);
        return i;
    }

    /**
     *
     * @param hb
     * @return
     */
    public int errorUpdate(HbInfo hb){
        String sql="UPDATE lottery_hb " +
                    "  set status=?," +
                    "      update_at=?," +
                    "      reason=?" +
                    "     where hb_code=?";
        Object[] params=new Object[]{
                hb.getStatus(),
                hb.getUpdateAt(),
                hb.getReason(),
                hb.getHbCode()
        };
        return jdbcTemplate.update(sql,params);
    }

    /**
     *
     * @param hb
     * @return
     */
    public int Update(HbInfo hb){
        String sql="UPDATE lottery_hb " +
                "set total_amount=?," +
                "    refund_amount=?," +
                "    reason=?," +
                "    status=?," +
                "    update_at=?," +
                "    refund_time=?," +
                "    rcv_time=? where hb_code=?";
        Object[] params=new Object[]{
                hb.getTotalAmount(),
                hb.getRefundAmount(),
                hb.getReason(),
                hb.getStatus(),
                hb.getUpdateAt(),
                hb.getRefundTime(),
                hb.getRcvTime(),
                hb.getHbCode()
        };
        return jdbcTemplate.update(sql,params);
    }



    /**
     *
     */
    private RowMapper<HbInfo> rowMapper=new RowMapper<HbInfo>() {
        @Override
        public HbInfo mapRow(ResultSet resultSet, int i) throws SQLException {
            HbInfo hb=new HbInfo();
            hb.setId(resultSet.getLong("id"));
            hb.setHbCode(resultSet.getString("hb_code"));
            hb.setTotalNum(resultSet.getInt("total_num"));
            hb.setTotalAmount(resultSet.getInt("total_amount"));
            hb.setRefundAmount(resultSet.getInt("refund_amount"));
            hb.setRefundTime(resultSet.getTimestamp("refund_time"));
            hb.setRcvTime(resultSet.getTimestamp("rcv_time"));
            hb.setAmount(resultSet.getInt("amount"));
            hb.setOpenid(resultSet.getString("openid"));
            hb.setNickName(resultSet.getString("nick_name"));
            hb.setActName(resultSet.getString("act_name"));
            hb.setSendName(resultSet.getString("send_name"));
            hb.setWishing(resultSet.getString("wishing"));
            hb.setClientIp(resultSet.getString("client_ip"));
            hb.setRemark(resultSet.getString("remark"));
            hb.setReason(resultSet.getString("reason"));
            hb.setStatus(resultSet.getByte("STATUS"));
            hb.setCreateAt(resultSet.getTimestamp("create_at"));
            hb.setUpdateAt(resultSet.getTimestamp("update_at"));

            return hb;
        }
    };

    private RowMapper<HbInfoSumVo> sumRowMapper=new RowMapper<HbInfoSumVo>() {
        @Override
        public HbInfoSumVo mapRow(ResultSet resultSet, int i) throws SQLException {
            HbInfoSumVo hb=new HbInfoSumVo();

            hb.setTotal(resultSet.getInt(1));
            hb.setTotalAmount(resultSet.getInt(2));
            hb.setRefundAmount(resultSet.getInt(3));

            return hb;
        }
    };
}
